<?php
//
//  REPORT CLASS
//


class Report {

    public $report_dir = 'reports/';
    public $filename;
    public $url;
    public $file_type = 'xls';
    public $category;
    public $datestamp;
    public $report_id;
    private $buffer;
    private $open_file;
    private $body;
    
    protected static $table_name = "_report";
    
    public static function save_report($filename, $arrays, $category) {
        global $session;
            $report = new Report;
            $report->filename = $filename;
            $report->category = $category;
            $report->body = self::convert_to_xls($arrays);
            $report->_save();
            return $report->url;
    }
    
    
    
    private function _save(){
        global $session;
        
        $this->url = $this->report_dir . $this->filename . date( '(dMY.H:i:s)', time() ) . '.' . $this->file_type;

        $this->open_file = fopen($this->url, "wb");
            if (is_resource($this->open_file)){
                
                fwrite($this->open_file, $this->body);
                fclose($this->open_file);
                
                if ( $this->_insert() && isset($session) ) {
                    $session->add_message('SUCCESS: Report has been saved. <br> <strong>Download Report: <a href="'.$this->url.'" > Here. </a></strong>');
                    return true;
                }
                             
            } else {
               die("Cannot open file"); 
               $session->add_message('FATAL ERROR: Invalid Filename.');
            }
    }
    
    public static function do_report( ) {
        global $session; 
        /**
         * 
         * Find out what report the user wants
         * 
         */
        switch( $_POST['action'] ) {
            case 'report_mailing_labels':
                $report_arrays = Report::get_labels($session->publication->publication_id, 'active');
                $report_filename = "mailing_labels_For_WK" . get_weeknum();
            break;
            
            case 'report_expiring_expired_labels':
                $report_arrays = Report::get_labels($session->publication->publication_id, 'expiring_expired');
                $report_filename = "expiring_expired_labels_For_WK" . get_weeknum();
            break;
            
            case 'report_recently_labels':
                $report_arrays = Report::get_labels($session->publication->publication_id, 'recently');
                $report_filename = "recently_expired_labels_For_WK" . get_weeknum();
            break;
            
            case 'report_expired_labels':
                $report_arrays = Report::get_labels($session->publication->publication_id, 'expired');
                $report_filename = "expired_labels_For_WK" . get_weeknum();
            break;
            case 'report_weekly_totals':
                $report_arrays = Report::get_totals_report($session->publication->publication_id);
                $report_filename = "weekly_totals_For_WK" . get_weeknum();
            break;
            case 'report_active_subs':
                $report_arrays = Report::get_subscription_report($session->publication->publication_id, 'all');
                $report_filename = "subscription_export_request" . get_weeknum();
            break;
            case 'report_expiring_subs':
                $report_arrays = Report::get_subscription_report($session->publication->publication_id, 'expiring');
                $report_filename = "inactive_subscription_export_request" . get_weeknum();  
            break;
            case 'report_user':
                $report_arrays = Report::get_user_report($_GET['id']);
                $report_filename = "user_export_request" . get_weeknum();
            break;
            case 'report_export_data': 
                $report_arrays = Report::get_export( $session->publication->publication_id );
                $report_filename = "data_export_request" . get_weeknum();
            break;
        }
        
        
        if( isset($report_filename) ) {
            if( ( isset($report_arrays) && !empty($report_arrays) ) ) {
                Report::save_report( $report_filename,  $report_arrays, 1);
            } else {
                $session->add_message('ERROR: This report request returned no results. It was not created.');
            }
        } else {
            $session->add_message('ERROR: This reporting action does not exits.');
        }
    }
    
    public static function prepare_sort_query( $sort_order ) {
        $sorts = explode(',', $sort_order );
        foreach( $sorts as $k => $sort ) {
             
             $sorts[$k] = User::get_tablename().'.'.$sort;
             if( $sort == 'box' || $sort == 'rr' || $sort == 'street_address' || $sort == 'apt' ) {
                $sorts[$k] = 'ABS('.$sorts[$k].')';
             }
             
        }
        $sort_order = implode(',',$sorts);
        return $sort_order;
    }
    
    
    
    public static function get_labels($publication_id, $type = 'active') {
         $pub = publication::find_by_id($publication_id);
         
         $result = array();
         $sort_order = self::prepare_sort_query( $pub->get_attr('setting_sort_order')  );
         
         view($pub);
         switch ( $type ) {
            case 'active':
                $subs = Subscription::find_all_within_tolerance_by_publication($publication_id, $pub->setting_expired_tolerance, 
                
                $filter = " AND ( status='active' OR status='expiring' OR status='inactive')", 
                
                $sort="
                    ORDER BY " . $sort_order);
                break;
            
            case 'expiring_expired':
                $subs = Subscription::find_by_publication($publication_id, 0, 
                
                $filter = " AND status='inactive' OR status='expiring'", 
                
                $sort="
                    ORDER BY " . $sort_order, 9999);
            break;
            
            case 'recently':
                $subs = Subscription::find_all_within_tolerance_by_publication($publication_id, $pub->setting_expired_tolerance, 
                
                $filter = " AND status='inactive'", 
                
                $sort="
                    ORDER BY " . $sort_order);
                break;
                
            case 'expired':
                $subs = Subscription::find_by_publication($publication_id, 0, 
                
                $filter = " AND status='inactive'", 
                
                $sort="
                    ORDER BY " . $sort_order, 9999);
                break;
        }
         

        if( !empty($subs) && is_array($subs) ) { 
            foreach ($subs as $sub ) {
                   $data[] = array(
                    'FNAME'         => $sub->user->get_attr('firstname'),
                    'LNAME'         => $sub->user->get_attr('lastname'),
                    'EXPIRE'        => strtoupper( $sub->get_end_date() ),
                    'ADDRESS'       => $sub->user->get_simple_location(),
                    'CITY'          => $sub->user->get_attr('city'),
                    'PROVINCE'      => $sub->user->get_attr('province'),
                    'POSTCODE'      => strtoupper($sub->user->get_attr('postal_code')),
                    );
            }
        } else {
            return false;
        }

        return $data;
    }


    public static function get_export( $publication_id ) {
        // Defaults to all non-expired.
        $pub = publication::find_by_id($publication_id);
        $sort_order = self::prepare_sort_query( $pub->get_attr('setting_sort_order')  );
        
        //find_by_publication($publication_id, $offset = 0, $filter='', $sort='', $count = null) {
        $subs = Subscription::find_by_publication(
                    $publication_id, // $publication_id
                    0,               // $offset
                    '',              // $filter
                    " ORDER BY " . Subscription::get_tablename().'.status,' . $sort_order, //$sort
                    9999             // $count
                    );

        if( !empty($subs) && is_array($subs) ) {

            foreach ($subs as $sub ) {
                   $result[] = array(
                    'status'            => $sub->get_status(),
                    'user_id'           => $sub->user->user_id,
                    'user_datestamp'    => $sub->user->user_datestamp,
                    'firstname'         => $sub->user->firstname,
                    'lastname'          => $sub->user->lastname,
                    'displayname'       => $sub->user->displayname,
                    'phone'             => $sub->user->phone,
                    'email'             => $sub->user->email,
                    'apt'               => $sub->user->apt,
                    'box'               => $sub->user->box,
                    'rr'                => $sub->user->rr,
                    'street_address'    => $sub->user->street_address,
                    'street'            => $sub->user->street,
                    'city'              => $sub->user->city,
                    'postal_code'       => $sub->user->postal_code,
                    'province'          => $sub->user->province,
                    'country'           => $sub->user->country,
                    'subscription_id'   => $sub->subscription_id,
                    'subscription_datestamp'     => $sub->subscription_datestamp,
                    'start_date'        => $sub->start_date,
                    'start_week'        => $sub->start_week,
                    'subscription_length'        => $sub->subscription_length,
                    'subscription'      => implode(',', $sub->subscription),
                    'Issues Left'       => ($sub->subscription_length - $sub->get_current_week() ),
                    'Expires On'        => date("F j Y", strtotime($sub->get_end_date())),
                    );
            }
        } else {
            return false;
        }
        return $result;
    }


    public static function get_subscription_report($publication_id, $type = "all") {
        // Defaults to all non-expired.
         switch ( $type ) {
            
            case 'all':
                $subs = Subscription::find_by_publication($publication_id, 0, 
                
                $filter = " AND status!='inactive'", 
                
                $sort="
                    ORDER BY postal_code, rr, box, street, street_address, apt
                ", 9999);
                break;
            case 'expiring':
                $subs = Subscription::find_by_publication($publication_id, 0, 
                
                $filter = " AND status='expiring'", 
                
                $sort="
                    ORDER BY postal_code, rr, box, street, street_address, apt
                ", 9999);
                break;
        }
         
        if( !empty($subs) && is_array($subs) ) {

            foreach ($subs as $sub ) {
                   $result[] = array(
                    'Name' => $sub->user->displayname(),
                    'Status' => $sub->get_status(),
                    'Current' => $sub->get_current_week(),
                    'Total' => $sub->subscription_length,
                    'Issues Left' => ($sub->subscription_length - $sub->get_current_week() ),
                    'Expires On' => date("F j Y", strtotime($sub->get_end_date())),
                    'Postal Code' => $sub->user->postal_code,        
                    'Address' => $sub->user->get_simple_location(),
                    'City' => $sub->user->city,
                    'Province' => $sub->user->province,
                    'Country' => $sub->user->country
                    );
            }
        } else {
            return false;
        }
        return $result;
        
    }
         
    public static function get_totals_report($publication_id) {
        global $provinces;
        
        $subs = Subscription::find_by_publication($publication_id, 0, $filter="AND ".Subscription::get_tablename().".status != 'inactive'", '', 9999);
        if ( !isset($subs) || empty($subs) ) return false;
        
        $pub = publication::find_by_id($publication_id);
        
        $local_count = 0;
        $regional_count = 0;
        $non_regional_count = 0;
        $national_count = 0;
        $foreign_count = 0;
        
        
        // Init local cities array
        $local_cities = explode(",", strtolower($pub->setting_local_locations));
        $local_cities = array_flip($local_cities);
        foreach ( $local_cities as $k => $v )
            $local_cities[$k] = 0;        
        // Init provinces array
        $province_counter = $provinces;
            foreach ( $province_counter as $k => $v )
                $province_counter[$k] = 0;
        
        
        
        foreach ($subs as $sub ) {
            // dont count these nerds.    
            if ( $sub->status == 'inactive' || $sub->trashed == true ) continue;
            
            if ( strtolower($sub->user->country) == strtolower($pub->setting_country) ) {
                $national_count ++;
                $province_counter[ strtolower($sub->user->province) ] ++;
                
                if ( strtolower($sub->user->province) == strtolower($pub->setting_province) ) {
                    $regional_count ++;
                    if ( array_key_exists( strtolower($sub->user->city), $local_cities) ) {
                        $local_cities[ strtolower($sub->user->city) ] ++;
                        $local_count ++;
                    }
                } else {
                    $non_regional_count ++;
                }
                
            } else {
                $foreign_count ++;
            }
            
            
        }  
        
        view($local_cities);

        
        
        $result = array();
        $result[] = array(
                    'Location' => 'Total Local Subscriptions (Settings > Local Towns)', 'Total Subscriptions' => $local_count
                    );
                    
        foreach ($local_cities as $city => $count ) {
             $result[] = array(
                    ucwords($city), $count
                    );            
        }
        
        $result[] = array(
                    "Subscriptions in {$pub->setting_province}. Not including Local Towns", ($regional_count - $local_count)
                    );   
                    
        foreach ($province_counter as $province => $count ) {
             $result[] = array(
                    strtoupper($province), $count
                    );            
        }
        
        $result[] = array(
                    "Total Subscriptions in {$pub->setting_country}", $national_count
                    );  
        $result[] = array(
                    "Subscriptions in {$pub->setting_country}. Not including {$pub->setting_province}.", ($non_regional_count)
                    );            
        return $result;
    }
    
    
    
    
    public static function convert_to_xls($arrays){
        
        $xls = new ExportXLS('temp');
        $count = 0;
        if ( is_array($arrays ) ) {
                $row = array_keys($arrays[0]);
                $xls->addHeader($row);
            
            foreach ($arrays as $row )
            {
                $row = array_values($row);
                $row = array($row);
                $xls->addRow($row);
            }
            return $xls->returnSheet();
        }
    }
    
    
    /**
     * 
     * 
     * 
     * 
     * Deletes a report record from the database, and removes the corresponding file.
     * 
     */
    public static function delete($report_id) {
        global $db;
        global $session;
        
        $report = Report::find_by_id($report_id);
        if (!$report) {
            $session->add_message("NOTICE: Report Id $report_id no longer exists.");
        }
        
        if ( !unlink( BASE_PATH . '/' . $report->url) ) {
            $session->add_message("NOTICE: Could not find the Report file for Report Id $report_id.");
        }
        
        $sql =  "DELETE FROM ".self::get_tablename()." WHERE report_id={$report_id}";
        $result = $db->query( $sql );
        
        if($result) {
            $session->add_message("SUCCESS: Report {$report->filename} has been successfully deleted.");
            return true;
        } else {
            $session->add_message("ERROR: Report Id $report_id could not be deleted.");
            return false;
        }
        
    } 
     
    public static function get_tablename() {
        global $TABLE_PREFIX;
        return $TABLE_PREFIX . htmlspecialchars(self::$table_name); 
        
    }  
    public function attr($attr) {
        echo htmlspecialchars($this->$attr);
    }
            
    private function _insert() {
        global $db;
        $sql =  "INSERT INTO ".self::get_tablename()." ";
        $sql .= "(datestamp, filename, file_type, url, category) ";
        $sql .= "VALUES ('".date( 'Y-m-d H:i:s', time() )."', '{$this->filename}', '{$this->file_type}', '{$this->url}', {$this->category})";
        
        $result = $db->query( $sql );
        
        if  ($result ) {
                
                $sql =  "SELECT LAST_INSERT_ID() AS report_id FROM ".self::get_tablename();
                
                $result_set = $db->query( $sql );
                $row = $db->fetch_array($result_set);
                return $row['report_id'];
            }
        return false;      
        
        
    }
    
    public static function find_reports( $category ) {
        global $db;
        $result_set = $db->query( "
         SELECT * FROM " . self::get_tablename() . " 
         WHERE category={$category} ORDER BY datestamp DESC" );
         
        if ( $db->affected_rows() > 0 ) {     
            $object_array = array();
            while ($row = $db->fetch_array($result_set)) {
                $object_array[] = self::_instantiate($row, true, true);
            }
            // RETURNS the OBJECT.
            return $object_array;
            } 
        return false;
    }
    
    public static function find_by_id($report_id) {
        global $db;
        $result_set = $db->query( "
         SELECT * FROM " . self::get_tablename() . " 
         WHERE report_id={$report_id} LIMIT 1" );
         
        if ( $db->affected_rows() > 0 ) {     
            $object_array = array();
            while ($row = $db->fetch_array($result_set)) {
                $object_array[] = self::_instantiate($row, true, true);
            }
            // RETURNS the OBJECT.
            return $object_array[0];
            } 
        return false;
    }   
    
    

   /*
     *  GLOBAL SQL FUNCTIONS
     * 
     */

    
    public static function find_by_sql ( $sql="") {
        global $db;
        $result_set = $db->query( $sql );
        
        $object_array = array();
        while ($row = $db->fetch_array($result_set)) {
            $object_array[] = self::_instantiate($row);
        }
        // RETURNS AN ARRAY OF OBJECTS.
        return $object_array;
    }
    
    public static function update_by_sql ( $sql="") {
        global $db;
        $result_set = $db->query( $sql );
        
        if ($db->affected_rows() == 1) return TRUE;
        
        return FALSE;
    }

    private static function _instantiate ($record) {
        //Check the record array for exist and array.
        $object = new self;
        foreach ( $record as $key=>$value ) {
            //  LOOP THROUGH EACH key/value PAIR IN RECORD...
            if ( $object->has_attribute($key)) {
                //  IF object HAS A MATCHING VARIABLE, SET IT AS value
                $object->$key = $value;
            }
        }
        return $object;
    }
    
    private function has_attribute ( $key ) {
        //  NOTE: INCLUDES PRIVATE ATTRIBUTES.
        $object_vars = get_object_vars($this);
        
        return array_key_exists($key, $object_vars);
        
    } 
     
 
    
}
    